Crispo - Excel Challenge 43 2025

excel-challenges
weekly-exercises
Easy Sunday Excel Challenge
Published

October 26, 2025

Illustration for Crispo - Excel Challenge 43 2025

Challenge Description

Easy Sunday Excel Challenge

⭐ Problem Solution Units Availability item 1 cat1

Solutions

library(tidyverse)
library(readxl)

path = "files/2025-10-26/Challenge 72.xlsx"
input = read_excel(path, range = "B3:F8", col_names = FALSE, col_types = "text")
test  = read_excel(path, range = "H3:K12", col_names = FALSE) %>%
  mutate(...1 = str_to_title(...1))

result = input %>%
  mutate(...1 = str_to_title(...1)) %>%
  mutate(measure = ifelse(!str_detect(...1, "item|Item"), ...1, NA)) %>%
  fill(measure, .direction = "down") %>%
  filter(measure != ...1) %>%
  pivot_longer(-c(measure, ...1, ...2), names_to = "item", values_to = "value") %>%
  pivot_wider(names_from = measure, values_from = value) %>%
  add_row(...1 = NA, ...2 = NA, item = "", Units = "Units", Availability = "Availability", .before = 1) %>%
  select(-item)

colnames(result) = colnames(test)

all.equal(result, test)
# [1] TRUE

  • Logic:

    • Reads the workbook range needed for the challenge

    • Reshapes the data to the grain required by the task

    • Builds the intermediate helper columns that drive the final answer

    • Uses direct text-pattern extraction instead of manual cleanup

  • Strengths:

    • The R solution stays compact and mirrors the workbook logic closely.

  • Areas for Improvement:

    • The code assumes the workbook layout and named ranges remain stable.

  • Gem:

    • The best part of the solution is choosing a tidy intermediate shape before producing the final answer.
import pandas as pd
import numpy as np

path = "files/2025-10-26/Challenge 72.xlsx"

input = pd.read_excel(path, header=None, skiprows=2, nrows=6, usecols="B:F", dtype=str)
test = pd.read_excel(path, header=None, skiprows=2, nrows=10, usecols="H:K", dtype=str)
test.iloc[:, 0] = test.iloc[:, 0].str.title()

input.iloc[:, 0] = input.iloc[:, 0].str.title()
input['measure'] = input.iloc[:, 0].where(~input.iloc[:, 0].str.contains("item", case=False), None)
input['measure'] = input['measure'].ffill()
filtered = input[input['measure'] != input.iloc[:, 0]]

longer = filtered.melt(id_vars=['measure', filtered.columns[0], filtered.columns[1]], 
                      var_name='item', value_name='value')
wider = longer.pivot_table(index=[filtered.columns[0], filtered.columns[1], 'item'],
                           columns='measure', values='value', aggfunc='first').reset_index()
wider = pd.concat([pd.DataFrame([{filtered.columns[0]: np.nan, filtered.columns[1]: np.nan, 'item': '', 'Units': 'Units', 'Availability': 'Availability'}]), wider], ignore_index=True)
wider = wider.drop('item', axis=1)
wider.columns = test.columns

print(wider.equals(test)) # True

  • Logic:

    • Reads the workbook range needed for the challenge

    • Reshapes the data to the grain required by the task

  • Strengths:

    • The Python version keeps the same rule in a direct pandas-oriented workflow.

  • Areas for Improvement:

    • As with the R version, any workbook layout change would require small adjustments.

  • Gem:

    • The implementation stays close to the stated challenge instead of adding unnecessary complexity.

Difficulty Level

This task is moderate:

  • It combines familiar Excel-style logic with at least one non-trivial reshape, grouping, or parsing step.

  • The answer depends on getting the output layout exactly right.